/*
 * Copyright © 2008 PCCW Solutions All right reserved.
 *
 */
package com.blues.framework.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.ResultSetHandler;

/**
 * Executes SQL queries with pluggable strategies for handling 
 * <code>ResultSet</code>s.  This class is thread safe.
 * 
 * @see ResultSetHandler
 */
public class QueryCaller extends BaseCaller {
	/**
     * 构造函数
     * 
     * @param dbConn <code>Connection</code> 参数设置数据库调用所使用的连接对象
     */
	public QueryCaller(Connection dbConn) {
		super(dbConn);
	}
	

    /**
     * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
     * 
     * @param conn The Connection to use to run the query.  The caller is
     * responsible for closing this Connection.
     * @param sql The SQL to execute.
     * @param params An array of query replacement parameters.  Each row in
     * this array is one set of batch replacement values. 
     * @return The number of rows updated per statement.
     * @throws SQLException if a database access error occurs
     * @since DbUtils 1.1
     */
    public int[] batch(Connection conn, String sql, Object[][] params)
        throws SQLException {

        PreparedStatement stmt = null;
        int[] rows = null;
        try {
            stmt = this.prepareStatement(conn, sql);

            for (int i = 0; i < params.length; i++) {
                this.fillStatement(stmt, params[i]);
                stmt.addBatch();
            }
            rows = stmt.executeBatch();

        } catch (SQLException e) {
            this.rethrow(e, sql, params);
        } finally {
            DbUtils.close(stmt);
        }

        return rows;
    }

    /**
     * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.  The 
     * <code>Connection</code> is retrieved from the <code>DataSource</code> 
     * set in the constructor.  This <code>Connection</code> must be in 
     * auto-commit mode or the update will not be saved. 
     * 
     * @param sql The SQL to execute.
     * @param params An array of query replacement parameters.  Each row in
     * this array is one set of batch replacement values. 
     * @return The number of rows updated per statement.
     * @throws SQLException if a database access error occurs
     * @since DbUtils 1.1
     */
    public int[] batch(String sql, Object[][] params) throws SQLException {
		return this.batch(this.getConnection(), sql, params);
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
     * parameters.
     * 
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @return The number of rows updated.
     * @throws SQLException if a database access error occurs
     */
    public int update(Connection conn, String sql) throws SQLException {
        return this.update(conn, sql, new Object[]{});
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
     * parameter.
     * 
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @return The number of rows updated.
     * @throws SQLException if a database access error occurs
     */
    public int update(Connection conn, String sql, Object param)
        throws SQLException {

        return this.update(conn, sql, new Object[] { param });
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query.
     * 
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param params The query replacement parameters.
     * @return The number of rows updated.
     * @throws SQLException if a database access error occurs
     */
    public int update(Connection conn, String sql, Object[] params)
        throws SQLException {

        PreparedStatement stmt = null;
        int rows = 0;

        try {
            stmt = this.prepareStatement(conn, sql);
            this.fillStatement(stmt, params);
            rows = stmt.executeUpdate();

        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        } finally {
            DbUtils.close(stmt);
        }

        return rows;
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement without
     * any replacement parameters. The <code>Connection</code> is retrieved 
     * from the <code>DataSource</code> set in the constructor.  This 
     * <code>Connection</code> must be in auto-commit mode or the update will 
     * not be saved. 
     * 
     * @param sql The SQL statement to execute.
     * @throws SQLException if a database access error occurs
     * @return The number of rows updated.
     */
    public int update(String sql) throws SQLException {
        return this.update(sql, new Object[]{});
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement with
     * a single replacement parameter.  The <code>Connection</code> is 
     * retrieved from the <code>DataSource</code> set in the constructor.
     * This <code>Connection</code> must be in auto-commit mode or the 
     * update will not be saved. 
     * 
     * @param sql The SQL statement to execute.
     * @param param The replacement parameter.
     * @throws SQLException if a database access error occurs
     * @return The number of rows updated.
     */
    public int update(String sql, Object param) throws SQLException {
        return this.update(sql, new Object[] { param });
    }

    /**
     * Executes the given INSERT, UPDATE, or DELETE SQL statement.  The 
     * <code>Connection</code> is retrieved from the <code>DataSource</code> 
     * set in the constructor.  This <code>Connection</code> must be in 
     * auto-commit mode or the update will not be saved. 
     * 
     * @param sql The SQL statement to execute.
     * @param params Initializes the PreparedStatement's IN (i.e. '?') 
     * parameters.
     * @throws SQLException if a database access error occurs
     * @return The number of rows updated.
     */
    public int update(String sql, Object[] params) throws SQLException {
        Connection conn = this.getConnection();

		return this.update(conn, sql, params);
    }
    

    /* (non-Javadoc)
     * @see com.pccw.commons.database.BaseCaller#query(java.sql.Connection, java.lang.String, java.lang.Object[], org.apache.commons.dbutils.ResultSetHandler)
     */
    public Object query(Connection conn, String sql, Object[] params,
            ResultSetHandler rsh) throws SQLException {

        PreparedStatement stmt = null;
        ResultSet rs = null;
        Object result = null;

        try {
            stmt = this.prepareStatement(conn, sql);
            this.fillStatement(stmt, params);
            rs = this.wrap(stmt.executeQuery());
            result = rsh.handle(rs);

        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        } finally {
            try {
                DbUtils.close(rs);
            } finally {
                DbUtils.close(stmt);
            }
        }

        return result;
    }
    
    /**
     * Factory method that creates and initializes a 
     * <code>PreparedStatement</code> object for the given SQL.  
     * <code>BaseCaller</code> methods always call this method to prepare 
     * statements for them.  Subclasses can override this method to provide 
     * special PreparedStatement configuration if needed.  This implementation
     * simply calls <code>conn.prepareStatement(sql)</code>.
     *  
     * @param conn The <code>Connection</code> used to create the 
     * <code>PreparedStatement</code>
     * @param sql The SQL statement to prepare.
     * @return An initialized <code>PreparedStatement</code>.
     * @throws SQLException if a database access error occurs
     */
    private PreparedStatement prepareStatement(Connection conn, String sql)
        throws SQLException {
            
        return conn.prepareStatement(sql);
    }
    
    /**
     * 用真实参数替换 <code>PreparedStatement</code> 指定的形参
     * @param stmt PreparedStatement 数据库执行对象
     * @param params Query replacement parameters; <code>null</code> is a valid
     * value to pass in.
     * @throws SQLException if a database access error occurs
     */
    private void fillStatement(PreparedStatement stmt, Object[] params)
        throws SQLException {

        if (params == null) {
            return;
        }

        for (int i = 0; i < params.length; i++) {
        	stmt.setObject(i + 1, params[i]);
        }
    }
    
    /**
     * Wrap the <code>ResultSet</code> in a decorator before processing it.
     * This implementation returns the <code>ResultSet</code> it is given
     * without any decoration.
     *
     * <p>
     * Often, the implementation of this method can be done in an anonymous 
     * inner class like this:
     * </p>
     * <pre> 
     * BaseCaller run = new BaseCaller() {
     *     protected ResultSet wrap(ResultSet rs) {
     *         return StringTrimmedResultSet.wrap(rs);
     *     }
     * };
     * </pre>
     * 
     * @param rs The <code>ResultSet</code> to decorate; never 
     * <code>null</code>.
     * @return The <code>ResultSet</code> wrapped in some decorator. 
     */
    private ResultSet wrap(ResultSet rs) {
        return rs;
    }
    /**
     * 执行有参sql
     * @param conn 数据库连接
     * @param procName sql
     * @param params 参数数组
     * @return 结果集
     */
    public ResultSet executeSQL(Connection conn, String sql, Object[] params) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = this.prepareStatement(conn, sql);
            this.fillStatement(stmt, params);
            rs = stmt.executeQuery();
        } catch (SQLException e) {
            this.rethrow(e, sql, params);
        }
        return rs;
    }
}
